﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient
Public Class ClientesDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeCliente(ByVal pIdCli As Integer, ByVal pRfcCli As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM clientes " & _
                                            "WHERE IdCli = ?IdCli AND RfcCli= ?RfcCli;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?IdCli", pIdCli)
                    myComm.Parameters.AddWithValue("?RfcCli", pRfcCli)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function existeCliente(ByVal pIdCli As Integer) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM clientes " & _
                                            "WHERE IdCli = ?IdCli;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?IdCli", pIdCli)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraCliente(ByVal pCliente As ClientesBE) As Integer
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO clientes(RfcCli, RazonSocial, Nombre, Telefono, Fax, " & _
                                                                "CorreoElect, Observaciones, IdExt, ContrIEPS, Calle, NoExterior, NoInterior, Colonia, " & _
                                                                "Localidad, Referencia, Municipio, Estado, Pais, CodigoPostal, Status, TipoPersona) " & _
                                            "VALUES (@RfcCli, @RazonSocial, @Nombre, @Telefono, @Fax, @CorreoElect, " & _
                                                    "@Observaciones, @IdExt, @ContrIEPS, @Calle, @NoExterior, @NoInterior, @Colonia, @Localidad, " & _
                                                    "@Referencia, @Municipio, @Estado, @Pais, @CodigoPostal, @Status, @tipoPersona);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@RfcCli", pCliente.RfcCli)
                    myComm.Parameters.AddWithValue("@RazonSocial", pCliente.RazonSocial)
                    myComm.Parameters.AddWithValue("@Nombre", pCliente.Nombre)
                    myComm.Parameters.AddWithValue("@Telefono", pCliente.Telefono)
                    myComm.Parameters.AddWithValue("@Fax", pCliente.Fax)
                    myComm.Parameters.AddWithValue("@CorreoElect", pCliente.CorreoElect)
                    myComm.Parameters.AddWithValue("@Observaciones", pCliente.Observaciones)
                    myComm.Parameters.AddWithValue("@IdExt", pCliente.IdExt)
                    myComm.Parameters.AddWithValue("@ContrIEPS", pCliente.ContrIEPS)
                    myComm.Parameters.AddWithValue("@Calle", pCliente.Calle)
                    myComm.Parameters.AddWithValue("@NoExterior", pCliente.NoExterior)
                    myComm.Parameters.AddWithValue("@NoInterior", pCliente.NoInterior)
                    myComm.Parameters.AddWithValue("@Colonia", pCliente.Colonia)
                    myComm.Parameters.AddWithValue("@Localidad", pCliente.Localidad)
                    myComm.Parameters.AddWithValue("@Referencia", pCliente.Referencia)
                    myComm.Parameters.AddWithValue("@Municipio", pCliente.Municipio)
                    myComm.Parameters.AddWithValue("@Estado", pCliente.Estado)
                    myComm.Parameters.AddWithValue("@Pais", pCliente.Pais)
                    myComm.Parameters.AddWithValue("@CodigoPostal", pCliente.CodigoPostal)
                    Select Case pCliente.Status
                        Case ClientesBE.EstatusCliente.Activo
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case ClientesBE.EstatusCliente.Inactivo
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    If (pCliente.RfcCli.Length = 12) Then
                        myComm.Parameters.AddWithValue("@tipoPersona", "M")
                    Else
                        myComm.Parameters.AddWithValue("@tipoPersona", "F")
                    End If

                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return CType(myComm.LastInsertedId, Integer)
                    Else
                        Return 0
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaCliente(ByVal pCliente As ClientesBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE clientes " & _
                                            "SET RazonSocial = @RazonSocial, Nombre = @Nombre, Telefono = @Telefono, Fax = @Fax, " & _
                                                "CorreoElect = @CorreoElect, Observaciones = @Observaciones, IdExt = @IdExt, " & _
                                                "ContrIEPS = @ContrIEPS, Calle = @Calle, NoExterior = @NoExterior, " & _
                                                "NoInterior = @NoInterior, Colonia = @Colonia, Localidad = @Localidad, " & _
                                                "Referencia = @Referencia, Municipio = @Municipio, Estado = @Estado, " & _
                                                "Pais = @Pais, CodigoPostal = @CodigoPostal, Status = @Status " & _
                                                "WHERE IdCli = @IdCli AND RfcCli= @RfcCli; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@RazonSocial", pCliente.RazonSocial)
                    myComm.Parameters.AddWithValue("@Nombre", pCliente.Nombre)
                    myComm.Parameters.AddWithValue("@Telefono", pCliente.Telefono)
                    myComm.Parameters.AddWithValue("@Fax", pCliente.Fax)
                    myComm.Parameters.AddWithValue("@CorreoElect", pCliente.CorreoElect)
                    myComm.Parameters.AddWithValue("@Observaciones", pCliente.Observaciones)
                    myComm.Parameters.AddWithValue("@IdExt", pCliente.IdExt)
                    myComm.Parameters.AddWithValue("@ContrIEPS", pCliente.ContrIEPS)
                    myComm.Parameters.AddWithValue("@Calle", pCliente.Calle)
                    myComm.Parameters.AddWithValue("@NoExterior", pCliente.NoExterior)
                    myComm.Parameters.AddWithValue("@NoInterior", pCliente.NoInterior)
                    myComm.Parameters.AddWithValue("@Colonia", pCliente.Colonia)
                    myComm.Parameters.AddWithValue("@Localidad", pCliente.Localidad)
                    myComm.Parameters.AddWithValue("@Referencia", pCliente.Referencia)
                    myComm.Parameters.AddWithValue("@Municipio", pCliente.Municipio)
                    myComm.Parameters.AddWithValue("@Estado", pCliente.Estado)
                    myComm.Parameters.AddWithValue("@Pais", pCliente.Pais)
                    myComm.Parameters.AddWithValue("@CodigoPostal", pCliente.CodigoPostal)
                    Select Case pCliente.Status
                        Case ClientesBE.EstatusCliente.Activo
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case ClientesBE.EstatusCliente.Inactivo
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@IdCli", pCliente.IdCli)
                    myComm.Parameters.AddWithValue("@RfcCli", pCliente.RfcCli)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Function cambiaStatus(ByVal pId As Integer, ByVal pRfc As String, ByVal setActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE clientes " & _
                                            "SET Status = @Estatus " & _
                                            "WHERE IdCli = @Id " &
                                                "AND RfcCli = @rfc; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (setActivo) Then
                        myComm.Parameters.AddWithValue("@Estatus", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Estatus", "I")
                    End If
                    myComm.Parameters.AddWithValue("@Id", pId)
                    myComm.Parameters.AddWithValue("@rfc", pRfc)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getCliente(ByVal IdCli As Integer, ByVal rfc As String) As ClientesBE
        Dim tmpCliente As ClientesBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdCli, RfcCli, RazonSocial, Nombre, Telefono, Fax, " & _
                                                "CorreoElect, Observaciones, IdExt, ContrIEPS, Calle, NoExterior, NoInterior, Colonia, " & _
                                                "Localidad, Referencia, Municipio, Estado, Pais, CodigoPostal, Status " & _
                                            "FROM clientes " & _
                                            "WHERE IdCli = @IdCli AND RfcCli = @RfcCli ;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdCli", IdCli)
                    myComm.Parameters.AddWithValue("@RfcCli", rfc)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpCliente = ClientesFact.Build(myDR)
                        Else
                            tmpCliente = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpCliente

        Catch ex As Exception
            Throw
        Finally
            tmpCliente = Nothing
        End Try
    End Function

    Public Function getListadoCliente(ByVal pRfcCli As String, ByVal pCorreoElect As String, ByVal pRazonSocial As String,
                                      ByVal pStatus As Enumeraciones.estatusParaBusquedas, ByVal pTipoPersona As Enumeraciones.tipoPersonas,
                                      ByVal pTipoContrib As Enumeraciones.tipoContribuyente,
                                      ByVal pRegistroInicial As Integer, ByVal pPageSize As Integer,
                                      ByVal pSortColumn As Enumeraciones.campoOrdenamiento, ByVal pSortDirection As Enumeraciones.tipoOrdenamiento,
                                      ByRef pTotalRegistros As Integer) As List(Of ClientesBE)
        Dim ClienteLs As List(Of ClientesBE) = New List(Of ClientesBE)
        Dim strQryStatus As String = ""
        Dim strTipoPersona As String = ""
        Dim strQryTipoContrib As String = ""

        Select Case pTipoPersona
            Case Enumeraciones.tipoPersonas.PersonaFisica
                strTipoPersona = "F"
            Case Enumeraciones.tipoPersonas.PersonaMoral
                strTipoPersona = "M"
            Case Else
                strTipoPersona = ""
        End Select

        Select Case pTipoContrib
            Case Enumeraciones.tipoContribuyente.Todos
                strQryTipoContrib = ""
            Case Enumeraciones.tipoContribuyente.Nacional
                strQryTipoContrib = " AND RfcCli <> 'XEXX010101000' AND RfcCli <> 'XAXX010101000' "
            Case Enumeraciones.tipoContribuyente.Extranjero
                strQryTipoContrib = " AND RfcCli = 'XEXX010101000' "
            Case Enumeraciones.tipoContribuyente.PublicoEnGeneral
                strQryTipoContrib = " AND RfcCli = 'XAXX010101000'"
        End Select

        If (pStatus <> Enumeraciones.estatusParaBusquedas.Todos) Then
            If (pStatus = Enumeraciones.estatusParaBusquedas.Activos) Then
                strQryStatus = " AND Status = 'A' "
            Else
                strQryStatus = " AND Status = 'I' "
            End If
        End If
        pTotalRegistros = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT SQL_CALC_FOUND_ROWS IdCli, RfcCli, RazonSocial, Nombre, Telefono, Fax, " & _
                                                "CorreoElect, Observaciones, IdExt, ContrIEPS, Calle, NoExterior, NoInterior, Colonia, " & _
                                                "Localidad, Referencia, Municipio, Estado, Pais, CodigoPostal, Status " & _
                                            "FROM clientes " & _
                                           "WHERE RfcCli LIKE @RfcCli " &
                                                    "AND CorreoElect LIKE @CorreoElect " &
                                                    "AND RazonSocial LIKE @RazonSocial " &
                                                    "AND TipoPersona LIKE @tipoPers " &
                                                    strQryStatus &
                                                    strQryTipoContrib &
                                            Enumeraciones.getOrderByStatement(Enumeraciones.catalogo.Clientes, pSortColumn, pSortDirection) &
                                           " LIMIT @regInic, @pageSize;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@RfcCli", "%" & pRfcCli & "%")
                    myComm.Parameters.AddWithValue("@CorreoElect", "%" & pCorreoElect & "%")
                    myComm.Parameters.AddWithValue("@RazonSocial", "%" & pRazonSocial & "%")
                    myComm.Parameters.AddWithValue("@tipoPers", "%" & strTipoPersona & "%")
                    myComm.Parameters.AddWithValue("@regInic", pRegistroInicial)
                    myComm.Parameters.AddWithValue("@pageSize", pPageSize)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Cliente As ClientesBE = ClientesFact.Build(myDR)
                            If (Not Cliente Is Nothing) Then
                                ClienteLs.Add(Cliente)
                            End If
                            Cliente = Nothing
                        End While
                    End Using
                    Using myComm2 As MySqlCommand = myConn.CreateCommand()
                        myComm2.CommandType = CommandType.Text
                        myComm2.CommandText = "SELECT FOUND_ROWS();"
                        pTotalRegistros = Convert.ToInt32(myComm2.ExecuteScalar())
                    End Using
                End Using
            End Using
            Return ClienteLs
        Catch ex As Exception
            Throw
        Finally
            ClienteLs = Nothing
            strQryStatus = Nothing
        End Try
    End Function

    Function getListadoClasificacionCliente() As List(Of String)
        Dim lsClasif As List(Of String) = New List(Of String)
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT ClasificacionCte " &
                                            "FROM clasificacioncte;"
                    myComm.CommandType = CommandType.Text
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            lsClasif.Add(convertDTFromDb.ConvertString(myDR.Item("ClasificacionCte")))
                        End While
                    End Using
                End Using
            End Using
            Return lsClasif
        Catch ex As Exception
            Throw
        Finally
            lsClasif = Nothing
        End Try
    End Function

    Function existeClasificacionCliente(ByVal pClasif As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM clasificacioncte " & _
                                            "WHERE ClasificacionCte = ?clasif;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?clasif", pClasif)
                    myConn.Open()
                    Return (Convert.ToInt16(myComm.ExecuteScalar()) > 0) 
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

#End Region

End Class
